package com.accounting.util;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.itextpdf.layout.element.Table;
import com.itextpdf.layout.properties.AreaBreakType;
import javafx.scene.control.Alert;
import com.accounting.db.DatabaseManager;
import javafx.stage.FileChooser;
import javafx.stage.Stage;
import java.io.File;
import java.sql.*;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;
import java.util.HashMap;
import java.util.Map;
import com.itextpdf.kernel.pdf.*;
import com.itextpdf.layout.Document;
import com.itextpdf.layout.element.*;
import com.itextpdf.layout.properties.TextAlignment;
import com.itextpdf.layout.properties.UnitValue;
import com.itextpdf.kernel.colors.ColorConstants;
import com.itextpdf.kernel.font.PdfFont;
import com.itextpdf.kernel.font.PdfFontFactory;
import java.text.DecimalFormat;
import com.itextpdf.kernel.events.PdfDocumentEvent;
import com.itextpdf.kernel.events.Event;
import com.itextpdf.kernel.events.IEventHandler;
import com.itextpdf.kernel.geom.Rectangle;
import com.itextpdf.kernel.pdf.canvas.PdfCanvas;
import com.itextpdf.layout.borders.SolidBorder;
import com.itextpdf.layout.element.LineSeparator;
import com.itextpdf.layout.properties.BorderRadius;
import java.awt.Color;
import com.itextpdf.layout.element.AreaBreak;
import com.itextpdf.kernel.pdf.canvas.draw.SolidLine;
import java.io.IOException;
import javafx.scene.control.TreeTableView;
import com.accounting.model.Asset;

public class ExportUtil {
    
    public static void exportToExcel(List<Asset> assets, File file) throws IOException {
        // 实现 Excel 导出逻辑
    }

    public static void exportToCSV(List<Asset> assets, File file) throws IOException {
        // 实现 CSV 导出逻辑
    }

    public static void exportToExcel(Stage stage, LocalDate startDate, LocalDate endDate) {
        FileChooser fileChooser = new FileChooser();
        fileChooser.setTitle("保存Excel文件");
        fileChooser.getExtensionFilters().add(
            new FileChooser.ExtensionFilter("Excel文件", "*.xlsx")
        );
        fileChooser.setInitialFileName("收支记录_" + 
            LocalDate.now().format(DateTimeFormatter.BASIC_ISO_DATE) + ".xlsx");
        
        File file = fileChooser.showSaveDialog(stage);
        if (file == null) return;

        try {
            // 创建收支明细数据
            List<Map<String, Object>> transactionData = getTransactionData(startDate, endDate);
            List<Map<String, Object>> summaryData = getSummaryData(startDate, endDate);

            // 设置表头样式
            WriteCellStyle headStyle = new WriteCellStyle();
            WriteFont headFont = new WriteFont();
            headFont.setBold(true);
            headStyle.setWriteFont(headFont);

            // 设置内容样式
            WriteCellStyle contentStyle = new WriteCellStyle();
            contentStyle.setWrapped(true);

            // 创建样式策略
            HorizontalCellStyleStrategy styleStrategy = 
                new HorizontalCellStyleStrategy(headStyle, contentStyle);

            // 写入多个sheet
            EasyExcel.write(file.getPath())
                .registerWriteHandler(styleStrategy)
                .sheet("收支明细")
                .head(getTransactionHeaders())
                .doWrite(transactionData);

            EasyExcel.write(file.getPath())
                .registerWriteHandler(styleStrategy)
                .sheet("统计分析")
                .head(getSummaryHeaders())
                .doWrite(summaryData);

            showExportSuccess();
        } catch (Exception e) {
            showExportError(e);
        }
    }

    private static List<List<String>> getTransactionHeaders() {
        List<List<String>> headers = new ArrayList<>();
        headers.add(List.of("日期"));
        headers.add(List.of("类型"));
        headers.add(List.of("类别"));
        headers.add(List.of("金额"));
        headers.add(List.of("描述"));
        return headers;
    }

    private static List<List<String>> getSummaryHeaders() {
        List<List<String>> headers = new ArrayList<>();
        headers.add(List.of("项目"));
        headers.add(List.of("金额"));
        return headers;
    }

    private static List<Map<String, Object>> getTransactionData(LocalDate startDate, LocalDate endDate) 
            throws SQLException {
        List<Map<String, Object>> data = new ArrayList<>();
        
        String sql = """
            SELECT date, type, category, amount, description 
            FROM transactions 
            WHERE date BETWEEN ? AND ?
            ORDER BY date DESC
        """;

        try (Connection conn = DatabaseManager.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, startDate.toString());
            pstmt.setString(2, endDate.toString());
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                Map<String, Object> row = new HashMap<>();
                row.put("日期", rs.getString("date"));
                row.put("类型", rs.getString("type"));
                row.put("类别", rs.getString("category"));
                row.put("金额", rs.getDouble("amount"));
                row.put("描述", rs.getString("description"));
                data.add(row);
            }
        }

        return data;
    }

    private static List<Map<String, Object>> getSummaryData(LocalDate startDate, LocalDate endDate) 
            throws SQLException {
        List<Map<String, Object>> data = new ArrayList<>();
        
        String sql = """
            SELECT 
                SUM(CASE WHEN type = '收入' THEN amount ELSE 0 END) as total_income,
                SUM(CASE WHEN type = '支出' THEN amount ELSE 0 END) as total_expense
            FROM transactions 
            WHERE date BETWEEN ? AND ?
        """;

        try (Connection conn = DatabaseManager.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, startDate.toString());
            pstmt.setString(2, endDate.toString());
            ResultSet rs = pstmt.executeQuery();

            if (rs.next()) {
                double income = rs.getDouble("total_income");
                double expense = rs.getDouble("total_expense");
                double balance = income - expense;

                Map<String, Object> incomeRow = new HashMap<>();
                incomeRow.put("项目", "总收入");
                incomeRow.put("金额", income);
                data.add(incomeRow);

                Map<String, Object> expenseRow = new HashMap<>();
                expenseRow.put("项目", "总支出");
                expenseRow.put("金额", expense);
                data.add(expenseRow);

                Map<String, Object> balanceRow = new HashMap<>();
                balanceRow.put("项目", "结余");
                balanceRow.put("金额", balance);
                data.add(balanceRow);
            }
        }

        return data;
    }

    private static class HeaderFooterHandler implements IEventHandler {
        private Document document;
        private String title;
        private PdfFont font;
        
        public HeaderFooterHandler(Document document, String title) {
            this.document = document;
            this.title = title;
            try {
                this.font = PdfFontFactory.createFont();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        
        @Override
        public void handleEvent(Event event) {
            try {
                PdfDocumentEvent docEvent = (PdfDocumentEvent) event;
                PdfCanvas canvas = new PdfCanvas(docEvent.getPage());
                Rectangle pageSize = docEvent.getPage().getPageSize();
                
                // 页眉
                canvas.beginText()
                    .setFontAndSize(font, 10)
                    .moveText(pageSize.getWidth() / 2 - 50, pageSize.getHeight() - 20)
                    .showText(title)
                    .endText();
                    
                // 页脚
                canvas.beginText()
                    .setFontAndSize(font, 8)
                    .moveText(pageSize.getWidth() / 2 - 30, 20)
                    .showText("第 " + docEvent.getDocument().getPageNumber(docEvent.getPage()) + " 页")
                    .endText();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    public static void exportToPDF(Stage stage, LocalDate startDate, LocalDate endDate) {
        FileChooser fileChooser = new FileChooser();
        fileChooser.setTitle("保存PDF文件");
        fileChooser.getExtensionFilters().add(
            new FileChooser.ExtensionFilter("PDF文件", "*.pdf")
        );
        fileChooser.setInitialFileName("收支记录_" + 
            LocalDate.now().format(DateTimeFormatter.BASIC_ISO_DATE) + ".pdf");
        
        File file = fileChooser.showSaveDialog(stage);
        if (file == null) return;

        try {
            PdfWriter writer = new PdfWriter(file);
            PdfDocument pdf = new PdfDocument(writer);
            Document document = new Document(pdf);
            
            // 添加页眉页脚
            pdf.addEventHandler(PdfDocumentEvent.START_PAGE, 
                new HeaderFooterHandler(document, "家庭记账系统 - 收支报告"));

            // 设置页边距
            document.setMargins(50, 30, 50, 30);

            // 添加标题和统计期间
            addHeader(document, startDate, endDate);
            
            // 添加总体统计
            addSummarySection(document, startDate, endDate);
            
            // 添加月度趋势分析
            addMonthlyTrendAnalysis(document, startDate, endDate);
            
            // 添加分类统计
            addCategoryAnalysis(document, startDate, endDate);
            
            // 添加收支比例分析
            addIncomeExpenseRatio(document, startDate, endDate);
            
            // 添加明细表格（新页面）
            document.add(new AreaBreak(AreaBreakType.NEXT_PAGE));
            addTransactionTable(document, startDate, endDate);

            document.close();
            showExportSuccess();
        } catch (Exception e) {
            showExportError(e);
        }
    }

    private static void addHeader(Document document, LocalDate startDate, LocalDate endDate) {
        Paragraph title = new Paragraph("家庭记账系统 - 收支报告")
            .setFontSize(24)
            .setTextAlignment(TextAlignment.CENTER)
            .setBold()
            .setMarginBottom(20);

        Paragraph period = new Paragraph(String.format("统计期间：%s 至 %s",
            startDate.format(DateTimeFormatter.ofPattern("yyyy年MM月dd日")),
            endDate.format(DateTimeFormatter.ofPattern("yyyy年MM月dd日"))))
            .setFontSize(14)
            .setTextAlignment(TextAlignment.CENTER)
            .setMarginBottom(30);

        document.add(title);
        document.add(period);
        
        SolidLine line = new SolidLine();
        line.setLineWidth(1);
        document.add(new LineSeparator(line).setMarginBottom(20));
    }

    private static void addSummarySection(Document document, LocalDate startDate, LocalDate endDate) 
            throws SQLException {
        document.add(new Paragraph("总体统计").setFontSize(16).setMarginTop(20));

        Table summaryTable = new Table(2);
        summaryTable.setWidth(UnitValue.createPercentValue(50));
        summaryTable.setMarginBottom(20);

        // 添加表头
        summaryTable.addHeaderCell(new Cell()
            .setBackgroundColor(ColorConstants.LIGHT_GRAY)
            .add(new Paragraph("项目")));
        summaryTable.addHeaderCell(new Cell()
            .setBackgroundColor(ColorConstants.LIGHT_GRAY)
            .add(new Paragraph("金额")));

        String sql = """
            SELECT 
                SUM(CASE WHEN type = '收入' THEN amount ELSE 0 END) as total_income,
                SUM(CASE WHEN type = '支出' THEN amount ELSE 0 END) as total_expense
            FROM transactions 
            WHERE date BETWEEN ? AND ?
        """;

        try (Connection conn = DatabaseManager.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, startDate.toString());
            pstmt.setString(2, endDate.toString());
            ResultSet rs = pstmt.executeQuery();

            if (rs.next()) {
                double income = rs.getDouble("total_income");
                double expense = rs.getDouble("total_expense");
                double balance = income - expense;
                DecimalFormat df = new DecimalFormat("#,##0.00");

                // 使用 iText 的 Cell 而不是 POI 的 Cell
                summaryTable.addCell(new Cell().add(new Paragraph("总收入")));
                summaryTable.addCell(new Cell().add(new Paragraph(df.format(income) + " 元")));
                
                summaryTable.addCell(new Cell().add(new Paragraph("总支出")));
                summaryTable.addCell(new Cell().add(new Paragraph(df.format(expense) + " 元")));
                
                summaryTable.addCell(new Cell().add(new Paragraph("结余")));
                summaryTable.addCell(new Cell().add(new Paragraph(df.format(balance) + " 元")));

                document.add(summaryTable);
            }
        }
    }

    private static void addMonthlyTrendAnalysis(Document document, LocalDate startDate, LocalDate endDate) 
            throws SQLException {
        document.add(new Paragraph("月度收支趋势").setFontSize(16).setMarginTop(20));

        Table table = new Table(4);
        table.setWidth(UnitValue.createPercentValue(100));
        
        // 添加表头
        table.addHeaderCell(createHeaderCell("月份"));
        table.addHeaderCell(createHeaderCell("收入"));
        table.addHeaderCell(createHeaderCell("支出"));
        table.addHeaderCell(createHeaderCell("结余"));

        String sql = """
            SELECT 
                strftime('%Y-%m', date) as month,
                SUM(CASE WHEN type = '收入' THEN amount ELSE 0 END) as income,
                SUM(CASE WHEN type = '支出' THEN amount ELSE 0 END) as expense
            FROM transactions 
            WHERE date BETWEEN ? AND ?
            GROUP BY month
            ORDER BY month
        """;

        try (Connection conn = DatabaseManager.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, startDate.toString());
            pstmt.setString(2, endDate.toString());
            ResultSet rs = pstmt.executeQuery();

            DecimalFormat df = new DecimalFormat("#,##0.00");
            while (rs.next()) {
                String month = rs.getString("month");
                double income = rs.getDouble("income");
                double expense = rs.getDouble("expense");
                double balance = income - expense;

                table.addCell(createCell(month));
                table.addCell(createCell(df.format(income)));
                table.addCell(createCell(df.format(expense)));
                table.addCell(createCell(df.format(balance)));
            }
        }

        document.add(table);
    }

    private static Cell createHeaderCell(String text) {
        return new Cell()
            .setBackgroundColor(ColorConstants.LIGHT_GRAY)
            .setTextAlignment(TextAlignment.CENTER)
            .setBold()
            .add(new Paragraph(text));
    }

    private static Cell createCell(String text) {
        return new Cell()
            .setTextAlignment(TextAlignment.CENTER)
            .add(new Paragraph(text));
    }

    private static void addTransactionTable(Document document, LocalDate startDate, LocalDate endDate) 
            throws SQLException {
        document.add(new Paragraph("收支明细").setFontSize(16).setMarginTop(20));

        Table table = new Table(5);
        table.setWidth(UnitValue.createPercentValue(100));

        // 添加表头
        String[] headers = {"日期", "类型", "类别", "金额", "描述"};
        for (String header : headers) {
            table.addHeaderCell(new Cell()
                .setBackgroundColor(ColorConstants.LIGHT_GRAY)
                .add(new Paragraph(header)));
        }

        // 查询数据
        String sql = """
            SELECT date, type, category, amount, description 
            FROM transactions 
            WHERE date BETWEEN ? AND ?
            ORDER BY date DESC
        """;

        try (Connection conn = DatabaseManager.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, startDate.toString());
            pstmt.setString(2, endDate.toString());
            ResultSet rs = pstmt.executeQuery();

            DecimalFormat df = new DecimalFormat("#,##0.00");
            while (rs.next()) {
                table.addCell(new Cell().add(new Paragraph(rs.getString("date"))));
                table.addCell(new Cell().add(new Paragraph(rs.getString("type"))));
                table.addCell(new Cell().add(new Paragraph(rs.getString("category"))));
                table.addCell(new Cell().add(new Paragraph(df.format(rs.getDouble("amount")))));
                table.addCell(new Cell().add(new Paragraph(rs.getString("description"))));
            }
        }

        document.add(table);
    }

    private static void addCategoryAnalysis(Document document, LocalDate startDate, LocalDate endDate) 
            throws SQLException {
        document.add(new Paragraph("分类统计").setFontSize(16).setMarginTop(20));

        String sql = """
            SELECT type, category, SUM(amount) as total 
            FROM transactions 
            WHERE date BETWEEN ? AND ? 
            GROUP BY type, category
            ORDER BY type, total DESC
        """;

        Table table = new Table(3);
        table.setWidth(UnitValue.createPercentValue(70));

        table.addHeaderCell(new Cell()
            .setBackgroundColor(ColorConstants.LIGHT_GRAY)
            .add(new Paragraph("类型")));
        table.addHeaderCell(new Cell()
            .setBackgroundColor(ColorConstants.LIGHT_GRAY)
            .add(new Paragraph("类别")));
        table.addHeaderCell(new Cell()
            .setBackgroundColor(ColorConstants.LIGHT_GRAY)
            .add(new Paragraph("金额")));

        try (Connection conn = DatabaseManager.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, startDate.toString());
            pstmt.setString(2, endDate.toString());
            ResultSet rs = pstmt.executeQuery();

            DecimalFormat df = new DecimalFormat("#,##0.00");
            while (rs.next()) {
                table.addCell(new Cell().add(new Paragraph(rs.getString("type"))));
                table.addCell(new Cell().add(new Paragraph(rs.getString("category"))));
                table.addCell(new Cell().add(new Paragraph(df.format(rs.getDouble("total")))));
            }
        }

        document.add(table);
    }

    private static void addIncomeExpenseRatio(Document document, LocalDate startDate, LocalDate endDate) 
            throws SQLException {
        document.add(new Paragraph("收支比例分析").setFontSize(16).setMarginTop(20));

        String sql = """
            SELECT type, SUM(amount) as total 
            FROM transactions 
            WHERE date BETWEEN ? AND ?
            GROUP BY type
        """;

        Table table = new Table(2);
        table.setWidth(UnitValue.createPercentValue(50));

        table.addHeaderCell(new Cell()
            .setBackgroundColor(ColorConstants.LIGHT_GRAY)
            .add(new Paragraph("类型")));
        table.addHeaderCell(new Cell()
            .setBackgroundColor(ColorConstants.LIGHT_GRAY)
            .add(new Paragraph("金额")));

        try (Connection conn = DatabaseManager.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, startDate.toString());
            pstmt.setString(2, endDate.toString());
            ResultSet rs = pstmt.executeQuery();

            DecimalFormat df = new DecimalFormat("#,##0.00");
            while (rs.next()) {
                table.addCell(new Cell().add(new Paragraph(rs.getString("type"))));
                table.addCell(new Cell().add(new Paragraph(df.format(rs.getDouble("total")))));
            }
        }

        document.add(table);
    }

    private static void showExportSuccess() {
        javafx.application.Platform.runLater(() -> {
            Alert alert = new Alert(Alert.AlertType.INFORMATION);
            alert.setTitle("导出成功");
            alert.setHeaderText(null);
            alert.setContentText("数据已成功导出到Excel文件！");
            alert.showAndWait();
        });
    }

    private static void showExportError(Exception e) {
        javafx.application.Platform.runLater(() -> {
            Alert alert = new Alert(Alert.AlertType.ERROR);
            alert.setTitle("导出失败");
            alert.setHeaderText(null);
            alert.setContentText("导出数据时发生错误：" + e.getMessage());
            alert.showAndWait();
        });
    }

    public static void exportAssetsToExcel(TreeTableView<Asset> assetTable, File file) throws IOException {
        // 实现导出到Excel的逻辑
    }
    
    public static void exportAssetsToCSV(TreeTableView<Asset> assetTable, File file) throws IOException {
        // 实现导出到CSV的逻辑
    }
} 